package com.feidee.data.report.dao.impl;

import com.feidee.data.report.constant.Constant;
import com.feidee.data.report.dao.IReportAndSourceStatusDao;
import com.feidee.data.report.model.Dependency.DataResourcesStatusModel;
import com.feidee.data.report.model.Dependency.ReportDependencyStatusModel;
import com.feidee.data.report.util.DbOperationUtil;
import com.feidee.data.report.util.ToolUtils;
import com.feidee.fdcommon.configuration.CustomConfiguration;
import com.feidee.fdcommon.constant.CommonConstant;
import com.feidee.fdfalcon.falcon.FalconAlarmService;
import com.uhb.base.util.ReportStatus;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;


public class ReportAndSourceStatusDaoImpl implements IReportAndSourceStatusDao {

    private static Logger logger = Logger.getLogger(ReportAndSourceStatusDaoImpl.class);
    private DataSource confDataSource;
    @Resource
    private DataSource cusMysqlDataSource;
    private DataSource metadataSource;


    /**
     * 获取报表执行状态
     *
     * @param reportKey
     * @return
     */
    @Override
    public String getReportRunStatus(String reportKey) {

        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        ResultSet statusRs = null;
        String getStatusSqlStr = "SELECT distinct `exec_status` FROM `report_exec_status` WHERE `report_key` = '"
                + reportKey + "'";
        String reportRunStatus = Constant.REPORT_DEPENDENCY_NO_EXECUTE;
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(getStatusSqlStr);
            statusRs = statusStatement.executeQuery();
            if (statusRs != null && statusRs.next()) {
                reportRunStatus = statusRs.getString("exec_status");
            }
            logger.info("获取报表 " + reportKey + " 运行状态结果:" + reportRunStatus);
        } catch (SQLException e) {
            logger.info("获取报表 " + reportKey + " 执行状态异常,默认返回报表未执行:"
                    + Constant.REPORT_DEPENDENCY_NO_EXECUTE + ",", e);
            return Constant.REPORT_DEPENDENCY_NO_EXECUTE;
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement, statusRs);
        }

        return reportRunStatus;

    }

    /**
     * 更改报表执行状态
     *
     * @param reportKey
     * @param status
     * @return
     */
    @Override
    public int changeReportRunStatus(String reportKey, String status) {

        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        String changeStatusSqlStr = "insert into `report_exec_status`(`report_key`,`exec_status`,`update_time`) "
                + " VALUES (?,?,?) on duplicate key update `report_key`=?,`exec_status`=?,`update_time`=?";
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(changeStatusSqlStr);
            statusStatement.setString(1, reportKey);
            statusStatement.setString(2, status);
            statusStatement.setString(3, ToolUtils.getTimeStr());
            statusStatement.setString(4, reportKey);
            statusStatement.setString(5, status);
            statusStatement.setString(6, ToolUtils.getTimeStr());
            statusStatement.execute();
            logger.info("更改报表 " + reportKey + " 执行状态为:" + status + " 操作成功!");
        } catch (SQLException e) {
            logger.info("更改报表 " + reportKey + " 执行状态为:" + status + " 操作异常,", e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement);
        }

        return 0;
    }

    /**
     * 更改执行成功报表更新时间用于前端展示
     *
     * @return
     */
    @Override
    public void updateReportRunTime(String reportKey, String status, String priority, long startTimestamp, String errorMsg) {

        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        String changeStatusSqlStr = "insert into report_monitor_result" +
                "(`ymd`,`report_key`,`exec_status`,`priority`,`start_time`,`end_time`," +
                "`cost_time`,`error_info`) VALUES (?,?,?,?,?,?,?,?) on duplicate key update `ymd`=?,`report_key`=?," +
                "`exec_status`=?,`priority`=?,`start_time`=?,`end_time`=?,`cost_time`=?,`error_info`=?";

        try {
            String ymd = ToolUtils.getCurrentDay(startTimestamp);
            String startTime = "";
            String endTime = "";
            String costTime = "";
            if (Constant.REPORT_DEPENDENCY_EXECUTING.equals(status)) {
                startTime = ToolUtils.getTimeStr(startTimestamp);
            } else if (!Constant.REPORT_DEPENDENCY_NO_EXECUTE.equals(status)
                    && !Constant.REPORT_DEPENDENCY_WAITTING_EXECUTE.equals(status)) {
                startTime = ToolUtils.getTimeStr(startTimestamp);
                long now = System.currentTimeMillis();
                endTime = ToolUtils.getTimeStr(now);
                costTime = ToolUtils.getFrontCostTimeStr(now - startTimestamp);
            }
            statusConn = cusMysqlDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(changeStatusSqlStr);
            statusStatement.setString(1, ymd);
            statusStatement.setString(2, reportKey);
            statusStatement.setString(3, status);
            statusStatement.setString(4, priority);
            statusStatement.setString(5, startTime);
            statusStatement.setString(6, endTime);
            statusStatement.setString(7, costTime);
            statusStatement.setString(8, errorMsg);
            statusStatement.setString(9, ymd);
            statusStatement.setString(10, reportKey);
            statusStatement.setString(11, status);
            statusStatement.setString(12, priority);
            statusStatement.setString(13, startTime);
            statusStatement.setString(14, endTime);
            statusStatement.setString(15, costTime);
            statusStatement.setString(16, errorMsg);
            statusStatement.execute();
            logger.info("更改报表 " + reportKey + " 执行状态:" + status + " 最后更新时间 操作成功!");
        } catch (Exception e) {
            logger.info("更改报表 " + reportKey + " 执行状态:" + status + " 最后更新时间 操作异常,", e);
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement);
        }

        //查找当天上午0-8时执行失败的报表，发现执行失败则立即发送预警短信！
        if (isFailed(status) && timeController(Calendar.getInstance().get(Calendar.HOUR_OF_DAY))) {
            sendMsg(reportKey, status);
        }
    }

    /**
     * @param time 当前时刻（小时）
     * @author CHQ
     * @description 判断报表执行失败时间是否在0-8时内
     */
    private boolean timeController(int time) {
        return time >= 0 && time <= 7;
    }

    /**
     * @param status 报表执行状态
     * @author CHQ
     * @description 判断报表是否
     */
    private boolean isFailed(String status) {
        return status.equals(ReportStatus.ZXSB.getStatus())
                || status.equals(ReportStatus.DDCS.getStatus())
                || status.equals(ReportStatus.DRSB.getStatus())
                || status.equals(ReportStatus.DRCS.getStatus());
    }

    /**
     * @author CHQ
     * @date 2019-11-19
     * @description 实时发送预警短信
     */
    private void sendMsg(String reportKey, String status) {
        Connection conn = null;
        PreparedStatement reportInfoStmt = null;
        ResultSet reportInfoRs = null;
        String applyName = null;
        String count = null;

        try {
            //打印当前时刻日志信息
            Calendar calendar = Calendar.getInstance();
            int curHour = calendar.get(calendar.HOUR_OF_DAY);
            logger.info("凌晨预警 当前时刻为：" + curHour);

            // 获取报表的中文名
            String findApplyNameSqlStr = "select applyname from custom_report_metadata " +
                    "where applysqlname=" + "'" + reportKey + "'";
            logger.info("凌晨预警 report_key:" + reportKey + "查找报表中文名sql：" + findApplyNameSqlStr);
            conn = confDataSource.getConnection();
            reportInfoStmt = conn.prepareStatement(findApplyNameSqlStr);
            reportInfoRs = reportInfoStmt.executeQuery();
            while (reportInfoRs.next()) {
                applyName = reportInfoRs.getString("applyname");
            }
            logger.info("凌晨预警 report_key:" + reportKey + "报表中文名：" + applyName);

            //清理数据库资源
            DbOperationUtil.cleanConnEnv(reportInfoStmt, reportInfoRs);

            //获取依赖该报表的报表数量
            String findCountSqlStr = "select count(distinct applysqlname) as count " +
                    "from custom_report_metadata where (report_dependency RLIKE '.*," + reportKey + "$' " +
                    "OR report_dependency RLIKE '^" + reportKey + ",.*' " +
                    "OR report_dependency RLIKE '.*," + reportKey + ",.*' " +
                    "OR report_dependency='" + reportKey + "')";
            logger.info("凌晨预警 report_key:" + reportKey + "查找下游依赖报表数量sql：" + findCountSqlStr);
            reportInfoStmt = conn.prepareStatement(findCountSqlStr);
            reportInfoRs = reportInfoStmt.executeQuery();
            while (reportInfoRs.next()) {
                count = reportInfoRs.getString("count");
            }
            logger.info("凌晨预警 report_key:" + reportKey + "下游依赖表数量：" + count);

            //获取报表执行失败的具体原因
            String failedMsg = "";
            switch (status) {
                case "ZXSB":
                    failedMsg = ReportStatus.ZXSB.getFailedMsg();
                    break;
                case "DDCS":
                    failedMsg = ReportStatus.DDCS.getFailedMsg();
                    break;
                case "DRCS":
                    failedMsg = ReportStatus.DRCS.getFailedMsg();
                    break;
                case "DRSB":
                    failedMsg = ReportStatus.DRSB.getFailedMsg();
                    break;
                default:
                    break;
            }

            //构造预警短信内容
            String content = "报表" + reportKey + "(" + applyName + ")" + "于" +
                    new SimpleDateFormat("HH:mm:ss").format(System.currentTimeMillis()) +
                    "执行失败(" + failedMsg + ")，下游依赖" + count + "张表，请关注！";
            logger.info("凌晨预警 短信内容：" + content);

            //发送预警短信
            FalconAlarmService.sendAlarm(content, "data.pm.alarm", "datafeidee",
                    CustomConfiguration.getString("message.pwd"));
            logger.info("凌晨预警 查询数据库正常结束！");
        } catch (Exception e) {
            logger.error("凌晨预警 获取上午0-8点执行失败的报表异常：", e);
        } finally {
            DbOperationUtil.cleanConnEnv(conn, reportInfoStmt, reportInfoRs);
        }
    }

    /**
     * 记录活跃机器IP
     *
     * @return
     */
    @Override
    public void recordHostIp() {

        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        String changeStatusSqlStr = "insert into `report_exec_status`(`report_key`,`exec_status`,`update_time`) "
                + "VALUES (?,?,?) on duplicate key update `report_key`=?,`exec_status`=?,`update_time`=?";
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(changeStatusSqlStr);
            statusStatement.setString(1, Constant.CURRENT_ALIVE_HOST_IP_KEY);
            statusStatement.setString(2, Constant.CURRENT_ALIVE_HOST_IP);
            statusStatement.setString(3, ToolUtils.getTimeStr());
            statusStatement.setString(4, Constant.CURRENT_ALIVE_HOST_IP_KEY);
            statusStatement.setString(5, Constant.CURRENT_ALIVE_HOST_IP);
            statusStatement.setString(6, ToolUtils.getTimeStr());
            statusStatement.execute();
            logger.info("更改当前活跃机房IP为: " + Constant.CURRENT_ALIVE_HOST_IP + " 操作成功!");
        } catch (Exception e) {
            logger.info("更改当前活跃机房IP为: " + Constant.CURRENT_ALIVE_HOST_IP + " 操作失败!", e);
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement);
        }
    }

    /**
     * 清除报表正在执行和等待执行状态
     *
     * @return
     */
    public int clearReportDdzxAndZzzxRunStatus() {
        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        String changeStatusSqlStr = "UPDATE `report_exec_status` SET `exec_status`='WZX' "
                + "WHERE `exec_status` in ('DDZX','ZZZX')";
        logger.info("清除报表执行状态SQL: " + changeStatusSqlStr);
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(changeStatusSqlStr);
            statusStatement.execute();
            logger.info("清除报表等待执行和正在执行状态成功!");
        } catch (SQLException e) {
            logger.info("清除报表等待执行和正在执行状态异常,", e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement);
        }

        return 0;
    }

    @Override
    public String getDataResourcesStatus(String dateResource) {
        Connection conn = null;
        Statement st = null;
        String metaStatus = null;
        try {
            conn = confDataSource.getConnection();
            st = conn.createStatement();
            String selectSql = "SELECT meta_status FROM report_metadata_status WHERE meta_key='"
                    + dateResource + "' AND SUBSTR(status_time FROM 1 FOR 10)=CURDATE()";
            ResultSet resultSet = st.executeQuery(selectSql);
            if (resultSet.next()) {
                metaStatus = resultSet.getString("meta_status");
            }
        } catch (Exception e) {
            logger.error("#### 数据源表: " + dateResource + " 获取更新状态失败: " + e.getMessage());
        } finally {
            DbOperationUtil.cleanConnEnv(conn, st);
        }
        return metaStatus;
    }

    /**
     * 检验报表依赖的源数据是否正常
     *
     * @param reportKey
     * @param dataResources
     * @param isScheduler
     * @param enqueueTime
     * @return 0    依赖报表校验通过
     * -1   依赖源表校验不通过,而且执行报表等待时间达到最大值
     * -2   依赖源表校验不通过,需要放回队列继续等待
     * -3   依赖源表执行失败，该特性校验不通过
     * -4   依赖源表获取状态失败，该特性校验不通过
     */
    @Override
    public String validSourceTblData(String forceExec, String reportKey, String dataResources, String isScheduler, long enqueueTime, boolean isPrint) {
        try {
            if ("1".equals(forceExec)) {
                printLog("报表 " + reportKey + " 强制不校验源数据表有效性!", isPrint);
                return "0€";
            }
            if (CommonConstant.ENV_TEST.equals(CustomConfiguration.getString(CommonConstant.ENV_KEY, CommonConstant.ENV_LIVE))) {
                printLog("测试环境强制不校验源数据表有效性!", isPrint);
                return "0€";
            }
            if ("1".equals(isScheduler)) {
                if (dataResources == null || "".equals(dataResources)) {
                    printLog("报表 " + reportKey + " 数据源为空!", isPrint);
                    return "0€";
                } else {
                    List<String> noStatusDataResources = new ArrayList<>();
                    List<String> failedDataResources = new ArrayList<>();
                    String[] dataResourceArray = dataResources.split(",");
                    for (String dataResource : dataResourceArray) {
                        dataResource = dataResource.toLowerCase();
                        String warehouse = dataResource.split("\\.")[0];
                        if (!Constant.CONTAIN_WAREHOUSE.contains(warehouse) || Constant.FILTER_TABLE.contains(dataResource)) {
                            if (ToolUtils.getNowHourAndMinute().compareTo(CustomConfiguration.getString("not.dataResources.run.time", "03:20")) < 0) {
                                printLog("报表 " + reportKey + " 包含需过滤数据源,且当前时间早于运行时间！", isPrint);
                                return "-2€(当前时间早于运行时间)";
                            }
                            continue;
                        }
                        String dataResourcesStatus = getDataResourcesStatus(dataResource);
                        if (dataResourcesStatus == null) {
                            printLog("报表 " + reportKey + " 未能获取数据源: " + dataResource + "状态!", isPrint);
                            noStatusDataResources.add(dataResource);
                        } else if (!dataResourcesStatus.equals("SUCCESS")) {
                            printLog("报表 " + reportKey + " 获取数据源: " + dataResource + "状态为: " + dataResourcesStatus, isPrint);
                            failedDataResources.add(dataResource);
                        }
                    }
                    if (failedDataResources.size() > 0) {
                        return "-3€(" + StringUtils.join(failedDataResources, ",") + ")";
                    } else if (noStatusDataResources.size() > 0) {
                        if ((System.currentTimeMillis() - enqueueTime) >=
                                CustomConfiguration.getLong("dataResources.wait.time", 6 * 60 * 60 * 1000)) {
                            return "-1€(" + StringUtils.join(noStatusDataResources, ",") + ")";
                        }
                        return "-2€(" + StringUtils.join(noStatusDataResources, ",") + ")";
                    }
                }
            } else {
                printLog("报表 " + reportKey + " 非数据源调度无需校验数据源!", isPrint);
                if (ToolUtils.getNowHourAndMinute().compareTo(CustomConfiguration.getString("not.dataResources.run.time", "03:20")) < 0) {
                    printLog("报表 " + reportKey + " 非数据源调度且当前运行时间早于指定运行时间！", isPrint);
                    return "-2€(当前时间早于运行时间)";
                }
            }
        } catch (Exception e) {
            logger.error("报表 " + reportKey + "校验依赖数据源异常！", e);
            return "-4€(校验依赖数据源未知异常)";
        }
        return "0€";
    }

    /**
     * 校验报表依赖的报表是否执行完成
     *
     * @param reportKey
     * @param dependedReportStr 依赖报表字符串,以逗号分隔
     * @return 0    依赖报表校验通过
     * -1   依赖报表校验不通过,而且执行报表等待时间达到最大值
     * -2   依赖报表校验不通过,需要放回队列继续等待
     * -3   依赖报表执行失败，该特性校验不通过
     * -4   依赖报表执行等待超时，该特性校验不通过
     */
    @Override
    public String validReportDependency(String forceExec, String reportKey, String dependedReportStr, long submitTime, boolean isPrint) {

        //如果不依赖任何报表,直接返回校验通过
        if ("".equals(dependedReportStr)) {
            return "0€";
        }
        //如果强制报表执行,直接返回校验通过
        if ("1".equals(forceExec)) {
            printLog("报表 " + reportKey + " 强制不校验依赖报表特性!", isPrint);
            return "0€";
        }
        //检查依赖报表是否完成
        if (!ToolUtils.isNotRealNull(dependedReportStr)) {
            dependedReportStr = "";
        }
        String[] dependedReportArray = dependedReportStr.split(Constant.REPORT_DEPENDENCY_SPLIT_FLAG);
        for (String dependedStr : dependedReportArray) {
            String reportRunStatus = getReportRunStatus(dependedStr);
            if (Constant.REPORT_DEPENDENCY_EXECUTE_FAILED.equals(reportRunStatus) || Constant.REPORT_DATARESOURCES_WAIT_OVERTIME.equals(reportRunStatus)
                    || Constant.REPORT_DATARESOURCES_EXECUTE_FAILED.equals(reportRunStatus)) {
                printLog("报表 " + reportKey + " 依赖报表 " + dependedStr + " 执行失败...", isPrint);
                return "-3€(" + dependedStr + ")";
            } else if (Constant.REPORT_DEPENDENCY_WAIT_OVERTIME.equals(reportRunStatus)) {
                printLog("报表 " + reportKey + " 依赖的报表 " + dependedStr + " 执行等待超时,未执行...", isPrint);
                return "-4€(" + dependedStr + ")";
            } else if (Constant.REPORT_DEPENDENCY_EXECUTING.equals(reportRunStatus)
                    || Constant.REPORT_DEPENDENCY_WAITTING_EXECUTE.equals(reportRunStatus)) {
                if ((System.currentTimeMillis() - submitTime) >= Constant.REPORT_WAIT_MAX_TIME) {
                    return "-1€(" + dependedStr + ")";
                } else {
                    return "-2€";
                }
            }
        }
        return "0€";
    }

    @Override
    public List<DataResourcesStatusModel> getDependencyDataResourcesStatus(String reportKey) {
        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        ResultSet statusRs = null;
        ArrayList<DataResourcesStatusModel> dataResourcesStatusModels = new ArrayList<>();
        String getStatusSqlStr = "SELECT DISTINCT data_resources FROM report_sql WHERE report_key = '"
                + reportKey + "'";
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(getStatusSqlStr);
            statusRs = statusStatement.executeQuery();
            if (statusRs != null && statusRs.next()) {
                String dataResourcesStr = statusRs.getString("data_resources");
                String[] dataResourcesArray = dataResourcesStr.split(",");
                for (String dataResources : dataResourcesArray) {
                    DataResourcesStatusModel dataResourcesStatusModel = new DataResourcesStatusModel();
                    String dataResourcesStatus = getDataResourcesStatus(dataResources);
                    dataResourcesStatusModel.setData_resource(dataResources);
                    if (dataResourcesStatus == null) {
                        dataResourcesStatusModel.setStatus(Constant.REPORT_DEPENDENCY_NO_EXECUTE);
                    } else if (dataResourcesStatus.equals("SUCCESS")) {
                        dataResourcesStatusModel.setStatus(Constant.REPORT_DEPENDENCY_EXECUTE_SUCCESS);
                    } else {
                        dataResourcesStatusModel.setStatus(Constant.REPORT_DEPENDENCY_EXECUTE_FAILED);
                    }
                    dataResourcesStatusModels.add(dataResourcesStatusModel);
                }
            }
            logger.info("报表 " + reportKey + " 获取依赖数据源状态成功！");
        } catch (SQLException e) {
            logger.info("报表 " + reportKey + " 获取依赖数据源状态失败！", e);
            return null;
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement, statusRs);
        }
        return dataResourcesStatusModels;
    }

    /**
     * @param downReportDependencyStatus 下游报表依赖集合
     * @param reportKey                  待获取依赖的报表key
     * @description 获取报表的下游报表依赖关系（一层）
     */
    @Override
    public void getDownDependencyReportDependencyStatus(
            Set<ReportDependencyStatusModel> downReportDependencyStatus, String reportKey) {
        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        ResultSet rs = null;
        ReportDependencyStatusModel reportDependencyModel;

        try {
            logger.info("开始获取报表: " + reportKey + "下游依赖！");

            String getStatusSqlStr = "select distinct a.report_key,if(exec_status is null,\"WZX\",exec_status) AS exec_status " +
                    "from ( select distinct report_key from report_sql where (report_dependency RLIKE '.*," + reportKey +
                    "$' OR report_dependency RLIKE '^" + reportKey + ",.*' OR report_dependency RLIKE '.*," + reportKey +
                    ",.*' OR report_dependency='" + reportKey + "')) a left join (select report_key,exec_status " +
                    "from report_exec_status " + "where SUBSTR(update_time from 1 for 10)=CURDATE()" + ") b "
                    + "on a.report_key=b.report_key";
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(getStatusSqlStr);
            rs = statusStatement.executeQuery();

            while (rs.next()) {
                if (!rs.getString("report_key").equals(reportKey)) {
                    String report_key = rs.getString("report_key");
                    String execStatus = rs.getString("exec_status");
                    reportDependencyModel = new ReportDependencyStatusModel();
                    reportDependencyModel.setReport_key(report_key);
                    reportDependencyModel.setStatus(execStatus);
                    downReportDependencyStatus.add(reportDependencyModel);
                }
            }

            logger.info("获取报表: " + reportKey + "下游依赖结束！");
        } catch (SQLException e) {
            logger.info("获取报表 " + reportKey + " 依赖异常！", e);
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement, rs);
        }

        return;
    }

    /**
     * @param upReportDependencyStatus 上游报表依赖集合
     * @param reportKey                待获取依赖的报表key
     * @description 获取报表的上游报表依赖关系（一层）
     */
    @Override
    public void getUpDependencyReportDependencyStatus(
            Set<ReportDependencyStatusModel> upReportDependencyStatus, String reportKey) {
        Connection connection = null;
        PreparedStatement getReportDependencyReportKeyStatement = null;
        PreparedStatement getReportKeyAndStatusStatement = null;
        ResultSet getKeyRs = null;
        ResultSet getKeyAndStatusRs = null;
        String report_keys = null;
        String[] report_keys_arr;
        ReportDependencyStatusModel reportDependencyStatusModel;

        try {
            logger.info("开始获取报表: " + reportKey + "上游依赖！");

            String getReportDependencyReportKeySqlStr = "select distinct report_dependency as report_key from report_sql " +
                    "where report_key ='" + reportKey + "'";
            connection = confDataSource.getConnection();
            getReportDependencyReportKeyStatement = connection.prepareStatement(getReportDependencyReportKeySqlStr);
            getKeyRs = getReportDependencyReportKeyStatement.executeQuery();

            while (getKeyRs.next()) {
                report_keys = getKeyRs.getString("report_key");
            }
            report_keys_arr = report_keys.split(",");

            for (String report_key : report_keys_arr) {
                if (!report_key.equals(reportKey)) {
                    String getReportKeyAndStatusSqlstr = "select report_key,if(exec_status is null,\"WZX\",exec_status) as exec_status " +
                            "from report_exec_status where report_key='" + report_key + "'";
                    getReportKeyAndStatusStatement = connection.prepareStatement(getReportKeyAndStatusSqlstr);
                    getKeyAndStatusRs = getReportKeyAndStatusStatement.executeQuery();
                    while (getKeyAndStatusRs.next()) {
                        String key = getKeyAndStatusRs.getString("report_key");
                        String status = getKeyAndStatusRs.getString("exec_status");
                        reportDependencyStatusModel = new ReportDependencyStatusModel();
                        reportDependencyStatusModel.setReport_key(key);
                        reportDependencyStatusModel.setStatus(status);
                        upReportDependencyStatus.add(reportDependencyStatusModel);
                    }
                }
            }

            logger.info("获取报表: " + reportKey + "上游依赖结束！");
        } catch (SQLException e) {
            logger.info("获取报表 " + reportKey + " 依赖异常！", e);
        } finally {
            DbOperationUtil.cleanConnEnv(connection, getReportDependencyReportKeyStatement, getKeyRs);
            DbOperationUtil.cleanConnEnv(getKeyAndStatusRs);
            DbOperationUtil.cleanConnEnv(getReportKeyAndStatusStatement);
        }

        return;
    }

    @Override
    public List<String> getResourcesDependencyReportList(String searchDependencyReportSql) {
        Connection statusConn = null;
        PreparedStatement statusStatement = null;
        ResultSet statusRs = null;
        List<String> resourcesDependencyReportList = new ArrayList<>();
        try {
            statusConn = confDataSource.getConnection();
            statusStatement = statusConn.prepareStatement(searchDependencyReportSql);
            statusRs = statusStatement.executeQuery();
            while (statusRs.next()) {
                String reportKey = statusRs.getString("report_key");
                resourcesDependencyReportList.add(reportKey);
            }
            logger.info("获取依赖数据源报表SQL: " + searchDependencyReportSql + "运行成功！");
        } catch (SQLException e) {
            logger.info("获取依赖数据源报表SQL: " + searchDependencyReportSql + " 运行失败！", e);
            return null;
        } finally {
            DbOperationUtil.cleanConnEnv(statusConn, statusStatement, statusRs);
        }
        return resourcesDependencyReportList;
    }

    /**
     * @param report_key 报表key
     * @author CHQ
     * @description 查找report_key对于在元数据表中的唯一applyId
     */
    @Override
    public String getReportApplyId(String report_key) {
        Connection getIdConn = null;
        PreparedStatement getIdStatement = null;
        ResultSet idRs = null;
        String applyId = null;
        String getApplyIdSqlStr = "SELECT id from custom_report_metadata where applysqlname='"
                + report_key + "'";
        try {
            getIdConn = confDataSource.getConnection();
            getIdStatement = getIdConn.prepareStatement(getApplyIdSqlStr);
            idRs = getIdStatement.executeQuery();
            while (idRs != null && idRs.next()) {
                applyId = idRs.getString("id");
            }
            logger.info("获取报表：" + report_key + " applyId SQL 运行成功");
        } catch (SQLException e) {
            logger.info("获取报表：" + report_key + " applyId SQL 运行失败");
            return null;
        } finally {
            DbOperationUtil.cleanConnEnv(getIdConn, getIdStatement, idRs);
        }
        return applyId;
    }

    @Override
    public List<String> getRslt(String sqlStr) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
       List<String> keyList = new ArrayList<>();

        try {
            //初始化连接
             conn = confDataSource.getConnection();
             ps = conn.prepareStatement(sqlStr);
             rs = ps.executeQuery();

             //获取结果报表的key,并放入集合中待返回
            while (rs.next()){
                String key = rs.getString("report_key");
                keyList.add(key);
            }
        }catch (Exception e){
            //异常处理
            logger.error("获取指定上游依赖报表的报表失败：",e);
            return null;
        }finally {
            //环境清理
            DbOperationUtil.cleanConnEnv(conn,ps,rs);
        }

        //结果返回
        return keyList;
    }

    private void printLog(String msg, boolean isPrint) {
        if (isPrint) {
            logger.info(msg);
        }
    }

    public DataSource getConfDataSource() {
        return confDataSource;
    }

    public void setConfDataSource(DataSource confDataSource) {
        this.confDataSource = confDataSource;
    }
}
